CREATE TABLE [dbo].[tb_Instalaciones]
(
[idInstalacion] [int] NOT NULL IDENTITY(1, 1),
[Descripcion] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Edificio] [int] NOT NULL,
[TipoDeInstalacion] [int] NOT NULL,
[CantUtil] [int] NULL,
[CantReparar] [int] NULL,
[FrecuenciaRevision] [int] NOT NULL,
[UltInspeccion] [datetime] NULL,
[EstadoGeneral] [int] NOT NULL,
[CostoCapital] [int] NULL,
[CostoAnualMantenim] [int] NULL,
[VidaUtilPromedio] [int] NULL,
[Observaciones] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER [dbo].[updateInstalacion] ON [dbo].[tb_Instalaciones] 
FOR UPDATE
AS

if (update(idInstalacion)) begin
	UPDATE tb_Inspecciones
	SET InstalacionAfec = (SELECT idInstalacion from inserted)
	WHERE InstalacionAfec in (SELECT idInstalacion from deleted)

	UPDATE tb_Reparaciones
	SET InstalacionAfec = (SELECT idInstalacion from inserted)
	WHERE InstalacionAfec in (SELECT idInstalacion from deleted)
end
GO
ALTER TABLE [dbo].[tb_Instalaciones] ADD CONSTRAINT [PK_tb_Instalaciones] PRIMARY KEY NONCLUSTERED  ([idInstalacion]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [idxInstalacionesEdificio] ON [dbo].[tb_Instalaciones] ([Edificio]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[tb_Instalaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_Instalaciones_tb_Edificios] FOREIGN KEY ([Edificio]) REFERENCES [dbo].[tb_Edificios] ([idEdificio]) NOT FOR REPLICATION
GO
ALTER TABLE [dbo].[tb_Instalaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_Instalaciones_tb_Estados] FOREIGN KEY ([EstadoGeneral]) REFERENCES [dbo].[tb_Estados] ([idEstado])
GO
ALTER TABLE [dbo].[tb_Instalaciones] WITH NOCHECK ADD CONSTRAINT [FK_tb_Instalaciones_tb_TiposDeInstalaciones] FOREIGN KEY ([TipoDeInstalacion]) REFERENCES [dbo].[tb_TiposDeInstalaciones] ([idTipoDeInstalacion])
GO
ALTER TABLE [dbo].[tb_Instalaciones] NOCHECK CONSTRAINT [FK_tb_Instalaciones_tb_Edificios]
GO
